Following are commonly used database restore options:
Restore Options
REPLACE:
The REPLACE option overrides several important safety checks that restore normally performs. The overridden checks are as follows:
- Restoring over an existing database with a backup taken of another database.
- With the REPLACE option, restore allows you to overwrite an existing database with whatever database is in the backup set, even if the specified database name differs from the database name recorded in the backup set. This can result in accidentally overwriting a database by a different database.
- Restoring over a database using the full or bulk-logged recovery model where a tail-log backup has not been taken and the STOPAT option is not used.
- With the REPLACE option, you can lose committed work, because the log written most recently has not been backed up.
- Overwriting existing files. For example, a mistake could allow overwriting files of the wrong type, such as .xls files, or that are being used by another database that is not online. Arbitrary data loss is possible if existing files are overwritten, although the restored database is complete.
Recovery state
To determine the state of the database after the store operation, you must select one of the options of the Recovery state panel.
RESTORE WITH RECOVERY
Recovers the database after restoring the final backup checked in the Backup sets to restoregrid on the General page. This is the default option and is equivalent to specifying WITH RECOVERY in a RESTORE statement.
Under the full recovery model or bulk-logged recovery model, choose this option only if you are restoring all the log files now.
RESTORE WITH NORECOVERY
Leaves the database in the restoring state. This allows you to restore additional backups in the current recovery path. To recover the database, you will have to perform a restore operation by using the RESTORE WITH RECOVERY option.
This option is equivalent to specifying WITH NORECOVERY in a RESTORE statement.
RESTORE WITH STANDBY
Leaves the database in a standby state, in which the database is available for limited read-only access. This option is equivalent to specifying WITH STANDBY in a RESTORE statement.
Choosing this option requires that you specify a standby file in the Standby file text box. The standby file allows the recovery effects to be undone.
Standby file
Specifies a standby file. You can browse for the standby file or enter its pathname directly in the text box.
Restore transaction log with standby option leaves the database read only mode. The uncommitted transaction are undone and saved in a file so that recover effects can be reversed when database is restored with recovery option.
A use case for this option is the Log shipping where a secondary database can be left in standby mode. The read load as in reports and large queries can be run on the standby secondary server so as to share the primary server load.
The T-SQL to restore transaction log in STANDBY mode is given below:
RESTORE LOG AdventureWorks FROM DISK='D:\Anurag\adventureworks_log_10.trn' WITH STANDBY='D:\Anurag\standby_file.tuf'
Displays a message each time another percentage completes, and is used to gauge progress. If percentage is omitted, SQL Server displays a message after each 10 percent is completed.
